package src.queries;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import  src.entities.DiagnosedPerson;
import 	src.entities.User;

public class ReportQueries extends UnsecuredQueries{

	public ReportQueries(Connection c, SecurityQueries security) {
		super(c, security);
	}

	public ArrayList<DiagnosedPerson> generateReport(String diagnosis, 
			java.util.Date fromDate, java.util.Date toDate) throws SQLException{

		ArrayList<DiagnosedPerson> dPerson = new ArrayList<DiagnosedPerson>();
		
		java.sql.Date sqlToDate = new java.sql.Date(toDate.getTime());
		java.sql.Date sqlFromDate = new java.sql.Date(fromDate.getTime());
		
		PreparedStatement p = makePreparedStatement(
				"Select first_name, last_name, address, phone, test_date "+ 
				"FROM persons p, radiology_record rr "+
				"WHERE rr.diagnosis = ? "+ 
				"AND rr.patient_name = p.user_name "+
				"AND rr.test_date < ? "+
				"AND rr.test_date > ? "+
				"AND rr.test_date <= ALL (SELECT test_date "+
				"FROM radiology_record "+
				"WHERE diagnosis = ? "+
		"AND patient_name = p.user_name)");

		p.setString(1, diagnosis);
		p.setDate(2, sqlToDate);
		p.setDate(3, sqlFromDate);
		p.setString(4, diagnosis);
		ResultSet rs = p.executeQuery();

		while(rs.next()){
			dPerson.add(new DiagnosedPerson(rs.getString("first_name")+" "+rs.getString("last_name"),
					rs.getString("address"), rs.getString("phone"), rs.getDate("test_date")));
		}

		return dPerson;

	}
	
	public ArrayList<String> getDiagnosisList() throws SQLException {
		PreparedStatement p = makePreparedStatement(
				"SELECT diagnosis FROM radiology_record GROUP BY diagnosis");
		ResultSet rs = p.executeQuery();
		
		ArrayList<String> diagnoses = new ArrayList<String>();
		
		while(rs.next()) {
			diagnoses.add(rs.getString("diagnosis"));
		}
		
		return diagnoses;
	}	
}


